--USE scm_main;
if exists (select 1
          from sysobjects
          where  id = object_id('bd_ZtBamCi')
          and type in ('P','PC'))
DROP PROCEDURE bd_ZtBamCi
GO
CREATE PROCEDURE bd_ZtBamCi
  @bh VARCHAR(20) --班次编号
AS
	SET NOCOUNT ON;

	       select
              *
         from (
						SELECT
                bh,
                case
                    when lx = 0 then '上下班'
                    when lx = 2 then '休息'
                    when lx = 1 then '加班'
                    else ''
                end as lx,
								lx as lxrt,
                parent,
                case
                  when sbsj>=0 then
                    CASE
                        WHEN sbsj/60>9 THEN CONVERT(VARCHAR(2),sbsj/60) ELSE '0'+CONVERT (VARCHAR(2),sbsj/60)
                    END + ':' +
                    CASE
                        WHEN sbsj%60>9 THEN CONVERT(VARCHAR(2),sbsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),sbsj%60)
                    END
                  else '-'+
                    case
                        when (sbsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(sbsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(sbsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (sbsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(sbsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(sbsj*-1)%60)
                    END
                end AS sbsj,

                case
                  when xbsj>=0 then
                    CASE
                        WHEN xbsj/60>9 THEN CONVERT(VARCHAR(2),xbsj/60) ELSE '0'+CONVERT (VARCHAR(2),xbsj/60)
                    END + ':' +
                    CASE
                        WHEN xbsj%60>9 THEN CONVERT(VARCHAR(2),xbsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),xbsj%60)
                    END
                  else '-'+
                    case
                        when (xbsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(xbsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(xbsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (xbsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(xbsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(xbsj*-1)%60)
                    END
                end AS xbsj,

                case
                  when sbks>=0 then
                    CASE
                        WHEN sbks/60>9 THEN CONVERT(VARCHAR(2),sbks/60) ELSE '0'+CONVERT (VARCHAR(2),sbks/60)
                    END + ':' +
                    CASE
                        WHEN sbks%60>9 THEN CONVERT(VARCHAR(2),sbks%60 ) ELSE '0'+CONVERT (VARCHAR(2),sbks%60)
                    END
                  else '-'+
                    case
                        when (sbks*-1)/60>9 THEN CONVERT(VARCHAR(2),(sbks*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(sbks*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (sbks*-1)%60>9 THEN CONVERT(VARCHAR(2),(sbks*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(sbks*-1)%60)
                    END
                end AS sbks,

                case
                  when xbjs>=0 then
                    CASE
                        WHEN xbjs/60>9 THEN CONVERT(VARCHAR(2),xbjs/60) ELSE '0'+CONVERT (VARCHAR(2),xbjs/60)
                    END + ':' +
                    CASE
                        WHEN xbjs%60>9 THEN CONVERT(VARCHAR(2),xbjs%60 ) ELSE '0'+CONVERT (VARCHAR(2),xbjs%60)
                    END
                  else '-'+
                    case
                        when (xbjs*-1)/60>9 THEN CONVERT(VARCHAR(2),(xbjs*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(xbjs*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (xbjs*-1)%60>9 THEN CONVERT(VARCHAR(2),(xbjs*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(xbjs*-1)%60)
                    END
                end AS xbjs,

                case
                  when cdsj>=0 then
                    CASE
                        WHEN cdsj/60>9 THEN CONVERT(VARCHAR(2),cdsj/60) ELSE '0'+CONVERT (VARCHAR(2),cdsj/60)
                    END + ':' +
                    CASE
                        WHEN cdsj%60>9 THEN CONVERT(VARCHAR(2),cdsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),cdsj%60)
                    END
                  else '-'+
                    case
                        when (cdsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(cdsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(cdsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (cdsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(cdsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(cdsj*-1)%60)
                    END
                end AS cdsj,

                case
                  when ztsj>=0 then
                    CASE
                        WHEN ztsj/60>9 THEN CONVERT(VARCHAR(2),ztsj/60) ELSE '0'+CONVERT (VARCHAR(2),ztsj/60)
                    END + ':' +
                    CASE
                        WHEN ztsj%60>9 THEN CONVERT(VARCHAR(2),ztsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),ztsj%60)
                    END
                  else '-'+
                    case
                        when (ztsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(ztsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(ztsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (ztsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(ztsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(ztsj*-1)%60)
                    END
                end AS ztsj,
                qdqt,
                case
                    when qdqt = 4 then 1
                    else 0
                end as sign,
                case
                    when (qdqt = 1 or qdqt=3)  then 1
                    else 0
                end as signIn,
                case
                    when (qdqt = 2 or qdqt=3) then 1
                    else 0
                end as signOut,
                bjcd,
                bjzt,
                types,
                mach,
								sbws,
								xbws,
                alld,
                allt,

                case
                  when repast_start_time>=0 then
                    CASE
                        WHEN repast_start_time/60>9 THEN CONVERT(VARCHAR(2),repast_start_time/60) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time/60)
                    END + ':' +
                    CASE
                        WHEN repast_start_time%60>9 THEN CONVERT(VARCHAR(2),repast_start_time%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time%60)
                    END
                  else '-'+
                    case
                        when (repast_start_time*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_start_time*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_start_time*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_start_time*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time*-1)%60)
                    END
                end AS repastStartTime,

                case
                  when repast_end_time>=0 then
                    CASE
                        WHEN repast_end_time/60>9 THEN CONVERT(VARCHAR(2),repast_end_time/60) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time/60)
                    END + ':' +
                    CASE
                        WHEN repast_end_time%60>9 THEN CONVERT(VARCHAR(2),repast_end_time%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time%60)
                    END
                  else '-'+
                    case
                        when (repast_end_time*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_end_time*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_end_time*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_end_time*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time*-1)%60)
                    END
                end AS repastEndTime,

                case
                  when repast_start_time2>=0 then
                    CASE
                        WHEN repast_start_time2/60>9 THEN CONVERT(VARCHAR(2),repast_start_time2/60) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time2/60)
                    END + ':' +
                    CASE
                        WHEN repast_start_time2%60>9 THEN CONVERT(VARCHAR(2),repast_start_time2%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time2%60)
                    END
                  else '-'+
                    case
                        when (repast_start_time2*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_start_time2*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time2*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_start_time2*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_start_time2*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time2*-1)%60)
                    END
                end AS repastStartTime2,

                case
                  when repast_end_time2>=0 then
                    CASE
                        WHEN repast_end_time2/60>9 THEN CONVERT(VARCHAR(2),repast_end_time2/60) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time2/60)
                    END + ':' +
                    CASE
                        WHEN repast_end_time2%60>9 THEN CONVERT(VARCHAR(2),repast_end_time2%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time2%60)
                    END
                  else '-'+
                    case
                        when (repast_end_time2*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_end_time2*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time2*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_end_time2*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_end_time2*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time2*-1)%60)
                    END
                end AS repastEndTime2,
                case
                    when is_enjoyment_subsidized = 0 then 'false'
                    when is_enjoyment_subsidized = 1 then 'true'
                    else 'false'
                end as enjoymentSubsidized,
							dbo.bd_fn_numToMin(middle_shift_start_time) as middleShiftStartTime,
							dbo.bd_fn_numToMin(middle_shift_end_time) as middleShiftEndTime,
							dbo.bd_fn_numToMin(night_shift_start_time) as nightShiftStartTime,
							dbo.bd_fn_numToMin(night_shift_end_time) as nightShiftEndTime,
							is_flextime as isFlextime,
							flextime_interval_begin as flextimeIntervalBegin,
							flextime_interval_end as flextimeIntervalEnd
            FROM
            zt_shid
            WHERE ( regserial IS NULL OR regserial = '' ) and lx = 0
        union all
            SELECT
                bh,
                case
                    when lx = 0 then '上下班'
                    when lx = 2 then '休息'
                    when lx = 1 then '加班'
                else ''
                end as lx,
								lx as lxrt,
                parent,
                case
                  when sbsj>=0 then
                    CASE
                        WHEN sbsj/60>9 THEN CONVERT(VARCHAR(2),sbsj/60) ELSE '0'+CONVERT (VARCHAR(2),sbsj/60)
                    END + ':' +
                    CASE
                        WHEN sbsj%60>9 THEN CONVERT(VARCHAR(2),sbsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),sbsj%60)
                    END
                  else '-'+
                    case
                        when (sbsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(sbsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(sbsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (sbsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(sbsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(sbsj*-1)%60)
                    END
                end AS sbsj,

                case
                  when xbsj>=0 then
                    CASE
                        WHEN xbsj/60>9 THEN CONVERT(VARCHAR(2),xbsj/60) ELSE '0'+CONVERT (VARCHAR(2),xbsj/60)
                    END + ':' +
                    CASE
                        WHEN xbsj%60>9 THEN CONVERT(VARCHAR(2),xbsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),xbsj%60)
                    END
                  else '-'+
                    case
                        when (xbsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(xbsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(xbsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (xbsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(xbsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(xbsj*-1)%60)
                    END
                end AS xbsj,

                case
                  when sbks>=0 then
                    CASE
                        WHEN sbks/60>9 THEN CONVERT(VARCHAR(2),sbks/60) ELSE '0'+CONVERT (VARCHAR(2),sbks/60)
                    END + ':' +
                    CASE
                        WHEN sbks%60>9 THEN CONVERT(VARCHAR(2),sbks%60 ) ELSE '0'+CONVERT (VARCHAR(2),sbks%60)
                    END
                  else '-'+
                    case
                        when (sbks*-1)/60>9 THEN CONVERT(VARCHAR(2),(sbks*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(sbks*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (sbks*-1)%60>9 THEN CONVERT(VARCHAR(2),(sbks*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(sbks*-1)%60)
                    END
                end AS sbks,

                case
                  when xbjs>=0 then
                    CASE
                        WHEN xbjs/60>9 THEN CONVERT(VARCHAR(2),xbjs/60) ELSE '0'+CONVERT (VARCHAR(2),xbjs/60)
                    END + ':' +
                    CASE
                        WHEN xbjs%60>9 THEN CONVERT(VARCHAR(2),xbjs%60 ) ELSE '0'+CONVERT (VARCHAR(2),xbjs%60)
                    END
                  else '-'+
                    case
                        when (xbjs*-1)/60>9 THEN CONVERT(VARCHAR(2),(xbjs*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(xbjs*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (xbjs*-1)%60>9 THEN CONVERT(VARCHAR(2),(xbjs*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(xbjs*-1)%60)
                    END
                end AS xbjs,

                case
                  when cdsj>=0 then
                    CASE
                        WHEN cdsj/60>9 THEN CONVERT(VARCHAR(2),cdsj/60) ELSE '0'+CONVERT (VARCHAR(2),cdsj/60)
                    END + ':' +
                    CASE
                        WHEN cdsj%60>9 THEN CONVERT(VARCHAR(2),cdsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),cdsj%60)
                    END
                  else '-'+
                    case
                        when (cdsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(cdsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(cdsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (cdsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(cdsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(cdsj*-1)%60)
                    END
                end AS cdsj,

                case
                  when ztsj>=0 then
                    CASE
                        WHEN ztsj/60>9 THEN CONVERT(VARCHAR(2),ztsj/60) ELSE '0'+CONVERT (VARCHAR(2),ztsj/60)
                    END + ':' +
                    CASE
                        WHEN ztsj%60>9 THEN CONVERT(VARCHAR(2),ztsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),ztsj%60)
                    END
                  else '-'+
                    case
                        when (ztsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(ztsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(ztsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (ztsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(ztsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(ztsj*-1)%60)
                    END
                end AS ztsj,
                qdqt,
                case
                    when qdqt = 4 then 1
                    else 0
                end as sign,
                case
                    when (qdqt = 1 or qdqt=3)  then 1
                    else 0
                end as signIn,
                case
                    when (qdqt = 2 or qdqt=3) then 1
                    else 0
                end as signOut,
                bjcd,
                bjzt,
                types,
                mach,
								sbws,
								xbws,
                alld,
                allt,

                case
                  when repast_start_time>=0 then
                    CASE
                        WHEN repast_start_time/60>9 THEN CONVERT(VARCHAR(2),repast_start_time/60) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time/60)
                    END + ':' +
                    CASE
                        WHEN repast_start_time%60>9 THEN CONVERT(VARCHAR(2),repast_start_time%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time%60)
                    END
                  else '-'+
                    case
                        when (repast_start_time*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_start_time*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_start_time*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_start_time*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time*-1)%60)
                    END
                end AS repastStartTime,

                case
                  when repast_end_time>=0 then
                    CASE
                        WHEN repast_end_time/60>9 THEN CONVERT(VARCHAR(2),repast_end_time/60) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time/60)
                    END + ':' +
                    CASE
                        WHEN repast_end_time%60>9 THEN CONVERT(VARCHAR(2),repast_end_time%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time%60)
                    END
                  else '-'+
                    case
                        when (repast_end_time*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_end_time*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_end_time*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_end_time*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time*-1)%60)
                    END
                end AS repastEndTime,

                case
                  when repast_start_time2>=0 then
                    CASE
                        WHEN repast_start_time2/60>9 THEN CONVERT(VARCHAR(2),repast_start_time2/60) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time2/60)
                    END + ':' +
                    CASE
                        WHEN repast_start_time2%60>9 THEN CONVERT(VARCHAR(2),repast_start_time2%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time2%60)
                    END
                  else '-'+
                    case
                        when (repast_start_time2*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_start_time2*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time2*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_start_time2*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_start_time2*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time2*-1)%60)
                    END
                end AS repastStartTime2,

                case
                  when repast_end_time2>=0 then
                    CASE
                        WHEN repast_end_time2/60>9 THEN CONVERT(VARCHAR(2),repast_end_time2/60) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time2/60)
                    END + ':' +
                    CASE
                        WHEN repast_end_time2%60>9 THEN CONVERT(VARCHAR(2),repast_end_time2%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time2%60)
                    END
                  else '-'+
                    case
                        when (repast_end_time2*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_end_time2*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time2*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_end_time2*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_end_time2*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time2*-1)%60)
                    END
                end AS repastEndTime2,
                case
                    when is_enjoyment_subsidized = 0 then 'false'
                    when is_enjoyment_subsidized = 1 then 'true'
                    else 'false'
                end as enjoymentSubsidized,
							dbo.bd_fn_numToMin(middle_shift_start_time) as middleShiftStartTime,
							dbo.bd_fn_numToMin(middle_shift_end_time) as middleShiftEndTime,
							dbo.bd_fn_numToMin(night_shift_start_time) as nightShiftStartTime,
							dbo.bd_fn_numToMin(night_shift_end_time) as nightShiftEndTime,
							is_flextime as isFlextime,
							flextime_interval_begin as flextimeIntervalBegin,
							flextime_interval_end as flextimeIntervalEnd
            FROM
            zt_shid
            WHERE ( regserial IS NULL OR regserial = '' )  and lx = 1
        union all
            SELECT
                bh,
                case
                    when lx = 0 then '上下班'
                    when lx = 2 then '休息'
                    when lx = 1 then '加班'
                else ''
                end as lx,
								lx as lxrt,
                parent,
                case
                  when sbsj>=0 then
                    CASE
                        WHEN sbsj/60>9 THEN CONVERT(VARCHAR(2),sbsj/60) ELSE '0'+CONVERT (VARCHAR(2),sbsj/60)
                    END + ':' +
                    CASE
                        WHEN sbsj%60>9 THEN CONVERT(VARCHAR(2),sbsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),sbsj%60)
                    END
                  else '-'+
                    case
                        when (sbsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(sbsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(sbsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (sbsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(sbsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(sbsj*-1)%60)
                    END
                end AS sbsj,

                case
                  when xbsj>=0 then
                    CASE
                        WHEN xbsj/60>9 THEN CONVERT(VARCHAR(2),xbsj/60) ELSE '0'+CONVERT (VARCHAR(2),xbsj/60)
                    END + ':' +
                    CASE
                        WHEN xbsj%60>9 THEN CONVERT(VARCHAR(2),xbsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),xbsj%60)
                    END
                  else '-'+
                    case
                        when (xbsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(xbsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(xbsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (xbsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(xbsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(xbsj*-1)%60)
                    END
                end AS xbsj,

                case
                  when sbks>=0 then
                    CASE
                        WHEN sbks/60>9 THEN CONVERT(VARCHAR(2),sbks/60) ELSE '0'+CONVERT (VARCHAR(2),sbks/60)
                    END + ':' +
                    CASE
                        WHEN sbks%60>9 THEN CONVERT(VARCHAR(2),sbks%60 ) ELSE '0'+CONVERT (VARCHAR(2),sbks%60)
                    END
                  else '-'+
                    case
                        when (sbks*-1)/60>9 THEN CONVERT(VARCHAR(2),(sbks*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(sbks*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (sbks*-1)%60>9 THEN CONVERT(VARCHAR(2),(sbks*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(sbks*-1)%60)
                    END
                end AS sbks,

                case
                  when xbjs>=0 then
                    CASE
                        WHEN xbjs/60>9 THEN CONVERT(VARCHAR(2),xbjs/60) ELSE '0'+CONVERT (VARCHAR(2),xbjs/60)
                    END + ':' +
                    CASE
                        WHEN xbjs%60>9 THEN CONVERT(VARCHAR(2),xbjs%60 ) ELSE '0'+CONVERT (VARCHAR(2),xbjs%60)
                    END
                  else '-'+
                    case
                        when (xbjs*-1)/60>9 THEN CONVERT(VARCHAR(2),(xbjs*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(xbjs*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (xbjs*-1)%60>9 THEN CONVERT(VARCHAR(2),(xbjs*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(xbjs*-1)%60)
                    END
                end AS xbjs,

                case
                  when cdsj>=0 then
                    CASE
                        WHEN cdsj/60>9 THEN CONVERT(VARCHAR(2),cdsj/60) ELSE '0'+CONVERT (VARCHAR(2),cdsj/60)
                    END + ':' +
                    CASE
                        WHEN cdsj%60>9 THEN CONVERT(VARCHAR(2),cdsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),cdsj%60)
                    END
                  else '-'+
                    case
                        when (cdsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(cdsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(cdsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (cdsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(cdsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(cdsj*-1)%60)
                    END
                end AS cdsj,

                case
                  when ztsj>=0 then
                    CASE
                        WHEN ztsj/60>9 THEN CONVERT(VARCHAR(2),ztsj/60) ELSE '0'+CONVERT (VARCHAR(2),ztsj/60)
                    END + ':' +
                    CASE
                        WHEN ztsj%60>9 THEN CONVERT(VARCHAR(2),ztsj%60 ) ELSE '0'+CONVERT (VARCHAR(2),ztsj%60)
                    END
                  else '-'+
                    case
                        when (ztsj*-1)/60>9 THEN CONVERT(VARCHAR(2),(ztsj*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(ztsj*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (ztsj*-1)%60>9 THEN CONVERT(VARCHAR(2),(ztsj*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(ztsj*-1)%60)
                    END
                end AS ztsj,
                qdqt,
                case
                    when qdqt = 4 then 1
                    else 0
                end as sign,
                case
                    when (qdqt = 1 or qdqt=3)  then 1
                    else 0
                end as signIn,
                case
                    when (qdqt = 2 or qdqt=3) then 1
                    else 0
                end as signOut,
                bjcd,
                bjzt,
                types,
                mach,
								sbws,
								xbws,
                alld,
                allt,

                case
                  when repast_start_time>=0 then
                    CASE
                        WHEN repast_start_time/60>9 THEN CONVERT(VARCHAR(2),repast_start_time/60) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time/60)
                    END + ':' +
                    CASE
                        WHEN repast_start_time%60>9 THEN CONVERT(VARCHAR(2),repast_start_time%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time%60)
                    END
                  else '-'+
                    case
                        when (repast_start_time*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_start_time*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_start_time*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_start_time*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time*-1)%60)
                    END
                end AS repastStartTime,

                case
                  when repast_end_time>=0 then
                    CASE
                        WHEN repast_end_time/60>9 THEN CONVERT(VARCHAR(2),repast_end_time/60) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time/60)
                    END + ':' +
                    CASE
                        WHEN repast_end_time%60>9 THEN CONVERT(VARCHAR(2),repast_end_time%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time%60)
                    END
                  else '-'+
                    case
                        when (repast_end_time*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_end_time*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_end_time*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_end_time*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time*-1)%60)
                    END
                end AS repastEndTime,

                case
                  when repast_start_time2>=0 then
                    CASE
                        WHEN repast_start_time2/60>9 THEN CONVERT(VARCHAR(2),repast_start_time2/60) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time2/60)
                    END + ':' +
                    CASE
                        WHEN repast_start_time2%60>9 THEN CONVERT(VARCHAR(2),repast_start_time2%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_start_time2%60)
                    END
                  else '-'+
                    case
                        when (repast_start_time2*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_start_time2*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time2*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_start_time2*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_start_time2*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_start_time2*-1)%60)
                    END
                end AS repastStartTime2,

                case
                  when repast_end_time2>=0 then
                    CASE
                        WHEN repast_end_time2/60>9 THEN CONVERT(VARCHAR(2),repast_end_time2/60) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time2/60)
                    END + ':' +
                    CASE
                        WHEN repast_end_time2%60>9 THEN CONVERT(VARCHAR(2),repast_end_time2%60 ) ELSE '0'+CONVERT (VARCHAR(2),repast_end_time2%60)
                    END
                  else '-'+
                    case
                        when (repast_end_time2*-1)/60>9 THEN CONVERT(VARCHAR(2),(repast_end_time2*-1)/60) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time2*-1)/60)
                    END + ':' +
                    CASE
                        WHEN (repast_end_time2*-1)%60>9 THEN CONVERT(VARCHAR(2),(repast_end_time2*-1)%60 ) ELSE '0'+CONVERT (VARCHAR(2),(repast_end_time2*-1)%60)
                    END
                end AS repastEndTime2,
                case
                    when is_enjoyment_subsidized = 0 then 'false'
                    when is_enjoyment_subsidized = 1 then 'true'
                    else 'false'
                end as enjoymentSubsidized,
							dbo.bd_fn_numToMin(middle_shift_start_time) as middleShiftStartTime,
							dbo.bd_fn_numToMin(middle_shift_end_time) as middleShiftEndTime,
							dbo.bd_fn_numToMin(night_shift_start_time) as nightShiftStartTime,
							dbo.bd_fn_numToMin(night_shift_end_time) as nightShiftEndTime,
							is_flextime as isFlextime,
							flextime_interval_begin as flextimeIntervalBegin,
							flextime_interval_end as flextimeIntervalEnd
            FROM
            zt_shid
            WHERE ( regserial IS NULL OR regserial = '' )  and lx = 2
        ) a  where a.parent =@bh

  SET NOCOUNT OFF;
